USE [BMIG_MENSUAL_POST_CADENA]

 -- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = '[dbo].[WASP_M0006_CARGA_TABLA]'
	   AND 	  type = 'P') 
	DROP PROCEDURE [dbo].[WASP_M0006_CARGA_TABLA]
GO

create PROCEDURE [dbo].[WASP_M0006_CARGA_TABLA] (@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8)) as 
begin
  SELECT     cre.aosuc AS AGENCOLOC
  , cre.aooper AS CRCREDITO
  , RTRIM(LTRIM(tra.pfndoc)) AS RUTTRABAJA
  , RTRIM(LTRIM(CAST(CONVERT(varchar, tra.Pffnac,112) AS decimal(8, 0)))) AS FNACITRABA
  , CASE WHEN (tra.Pfcant = 'M') THEN 0 ELSE 1 END AS SEXOTRABAJ
  , CASE WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 1 THEN '00000000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 2 THEN '0000000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 3 THEN '000000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 4 THEN '00000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 5 THEN '0000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 6 THEN '000' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 7 THEN '00' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) 
      + '00' WHEN len(ltrim(rtrim(CAST(cre.aoimp AS numeric)))) = 8 THEN '0' + CONVERT(varchar, CAST(cre.aoimp AS numeric)) + '00' 
    END AS CRMONTO, 
    COUNT(cuo.ppoper) AS CRCUOTAS
  , cre.aotasa
  , '000' + SUBSTRING(CONVERT(char, cre.aotasa), 1, 1) + SUBSTRING(CONVERT(char, cre.aotasa), 3, 2) + '00' AS CRTASA
  , 0 AS CRCLASE
  , CASE  WHEN (a.ctccli = 3) THEN 'T' 
          WHEN (a.ctccli = 4) THEN 'P' 
          ELSE 'A' 
    END AS CRTRAPEN
  into dbo.as_colocacion_mensual_temp                       
  FROM dbo.fsd010 AS cre WITH (nolock) 
    CROSS JOIN dbo.fsd601 AS cuo WITH (nolock) 
    CROSS JOIN dbo.fsr008 AS rel WITH (nolock) 
    CROSS JOIN dbo.fsd002 AS tra WITH (nolock) 
    CROSS JOIN dbo.fsd008 AS a WITH (nolock) 
    CROSS JOIN dbo.JT73109 AS coloc WITH (nolock)
  WHERE (cre.aocta = cuo.ppcta) 
    AND (cre.aooper = cuo.ppoper) 
    AND (cre.aosuc = cuo.ppsuc) 
    AND (cuo.ppsbop = 0) 
    AND (cuo.d601co = 'S') 
    AND (cre.aocta = rel.ctnro) 
    AND (rel.pendoc = tra.pfndoc) 
    AND (cre.aosbop = 0) 
    AND (cre.aocta <> 999999999) 
    AND (coloc.JT73109FAP BETWEEN @FECHA_INICIO AND @FECHA_TERMINO) 
    AND (rel.ctnro = a.ctnro) 
    AND (a.ctccli IN (3, 4, 5)) 
    AND (cre.aooper = coloc.JT73109FPA) 
    AND (cre.aosuc = coloc.JT73109SUC) 
    AND (NOT EXISTS (SELECT *
                     FROM   dbo.as_desistidos AS anu
                     WHERE  (cre.aocta = anu.hcta) 
                     AND (cre.aooper = anu.hoper) 
                     AND (cre.aosuc = anu.hsucur)))
  GROUP BY cre.aosuc, cre.aooper, cre.aoimp, cre.aotasa, cre.aotope, cre.aofval, tra.pfndoc, tra.Pffnac, tra.Pfcant, a.ctccli
end  